
import os, time, re
from pysqlite2 import dbapi2 as sqlite

def read_query(file):
    """Bit of a hack, but reads a single query by looking in the given file
       for lines containing ; (end of query)"""
    retval = ''
    semicolon_re = re.compile('.*;.*')
    for line in file:
        retval += line
        if semicolon_re.match(line):
            return retval
    return None

class Database:

    def __init__(self, filename):
        if filename == ':memory:':
            new_db = True
        else:
            new_db = not os.path.exists(filename)
        self.con = sqlite.connect(filename)
        self.cur = self.con.cursor()
        self.verbose = False
        if new_db:
            schema = open('schema.sql')
            query_str = read_query(schema)
            while query_str:
                self.query(query_str)
                query_str = read_query(schema)

    def __del__(self):
        self.cur.close()
        self.con.close()

    def __str__(self):
        return str(self.cur.fetchall())

    def query(self, query_str, args=()):
        if self.verbose:
            print query_str, args
        try:
            self.cur.execute(query_str, args)
            self.con.commit()
        except sqlite.Error, e:
            print 'sqlite error: ', e.args[0]

    def query_data(self, id):
        self.query('SELECT value FROM data WHERE id = ?', (id,))
        return self.cur

    def query_data_id(self, value):
        self.query('SELECT id FROM data WHERE value = ?', (value,))
        return self.cur

    def query_search_data(self, search_str):
        search_str = '%' + search_str + '%'
        self.query('SELECT id, value FROM data WHERE value LIKE ?', (search_str,))
        return self.cur

    def query_recent_data(self, limit=20):
        self.query('SELECT id, value, modified FROM data WHERE modified > 0 ORDER BY modified DESC LIMIT ?', (limit,))
        return self.cur

    def insert_data(self, value):
        result = self.query_data_id(value)
        if result.fetchone() == None:
            self.query('INSERT INTO data (value, modified) VALUES (?, ?)', (value, time.time()))
        # TODO: else update the timestamp
        return self.cur

    def query_data_types(self, data_id):
        self.query('SELECT to_id FROM is_a_links WHERE from_id = ?', (data_id,))
        return self.cur

    def query_data_members(self, data_id):
        self.query('SELECT to_id FROM has_a_links WHERE from_id = ?', (data_id,))
        return self.cur

    def query_all_tags(self):
        result = self.query('SELECT id FROM data WHERE value = "tag"')
        if result == None:
            return None
        tag_id = result.fetchone()[0]
        self.query('SELECT from_id FROM is_a_links WHERE to_id = %{tag_id}')
        return self.cur

